<?php
class Person_Controller extends AdminController {

    public function __construct() {
        parent::__construct('person');
        $this->setDefaultListOrder('last_name');
    }

    public function all() {
        $queryAndParams = $this->allData();
        $this->initPagination($queryAndParams);

        $this->viewData['personList'] = $this->dao->queryAll($this->orderLimitAndOffset($queryAndParams->query), $queryAndParams->params);

        // Quick search values
        $this->viewData['personOptions'] = $this->dao->queryOptions(
                'SELECT id, first_name, last_name, birth_date FROM person ' .
                'ORDER BY last_name ASC, first_name ASC, birth_date ASC',
                NULL, array(' ', '()'));

        $this->renderMySubview('personList');
    }

    /**
     * @return QueryAndParams
     */
    public function allData() {
        $query = 'SELECT p.*, c.name AS country_name, acc.login, acc.is_admin, acc.is_blocked ' .
                'FROM person AS p ' .
                'LEFT OUTER JOIN account acc ON acc.person_id = p.id ' .
                'LEFT OUTER JOIN country AS c ON p.country_code = c.code ';
        $queryParams = array();

        // Search criteria
        $where = $this->searchCriteriaToQuery();
        if ($where->query) {
            $query .= 'WHERE ' . $where->query;
        }

        return new QueryAndParams($query, $where->params);
    }

    private function searchCriteriaToQuery() {
        $query = '';
        $params = array();
        $searchCriteria = $this->getSearchCriteria();
        foreach ($searchCriteria as $key => $value) {
            if (preg_match('/first_name|last_name|telephone|street|city|email/', $key)) {
                $query .= 'p.' . $key . ' LIKE :' . $key . ' AND ';
                $params[$key] = '%' . $value . '%';
            }
            else if ($key == 'country_code') {
                $query .= 'c.code = :country_code AND ';
                $params[$key] = $value;
            }
            else if ($key == 'gender') {
                $query .= 'p.gender = :gender AND ';
                $params[$key] = $value;
            }
            else if ($key == 'is_ztp') {
                $query .= 'p.is_ztp = :is_ztp AND ';
                $params[$key] = $value;
            }
            else if ($key == 'person_tag_ids') {
                $query .= 'p.id IN (SELECT person_id FROM person_tag_asoc WHERE person_tag_id IN (' . sql::inClauseValues($value) . ')) AND ';
            }
            else if ($key == 'exclude_uninterested' && $value == sql::$TRUE) {
                $query .= 'p.id NOT IN (SELECT person_id FROM person_tag_asoc JOIN person_tag ON person_tag_id = id WHERE code = \'uninterested\') AND ';
            }
        }
        if ($query != '') {
            $query = substr($query, 0, -4);
        }
        return new QueryAndParams($query, $params);
    }

    public function exportToExcel() {
        $queryAndParams = $this->allData();
        $personList = $this->dao->queryAll($queryAndParams->query, $queryAndParams->params);
        
        excel::loadClasses();

        $objPHPExcel = new PHPExcel();

        // Metadata
        excel::setMetadata($objPHPExcel);
        $objPHPExcel->setActiveSheetIndex(0);
        $sheet = $objPHPExcel->getActiveSheet();

        // Headers
        $sheet->setCellValue('A1', text::get('person.last_name'));
        $sheet->setCellValue('B1', text::get('person.first_name'));
        $sheet->setCellValue('C1', text::get('person.birth_date'));
        $sheet->setCellValue('D1', text::get('person.email'));
        $sheet->setCellValue('E1', text::get('person.country_code'));
        $sheet->setCellValue('F1', text::get('person.login'));
        $sheet->setCellValue('G1', text::get('person.is_admin'));
        $sheet->setCellValue('H1', text::get('person.is_blocked'));

        // Values
        $rowNumber = 2;
        foreach ($personList as $person) {

            $sheet->setCellValue('A' . $rowNumber, $person['last_name']);
            $sheet->setCellValue('B' . $rowNumber, $person['first_name']);
            $sheet->setCellValue('C' . $rowNumber, format::date($person['birth_date']));
            $sheet->setCellValue('D' . $rowNumber, $person['email']);
            $sheet->setCellValue('E' . $rowNumber, $person['country_name']);
            $sheet->setCellValue('F' . $rowNumber, $person['login']);
            $sheet->setCellValue('G' . $rowNumber, format::boolean($person['is_admin']));
            $sheet->setCellValue('H' . $rowNumber, format::boolean($person['is_blocked']));
            $rowNumber ++;
        }

        // Borders
        // $objPHPExcel->getActiveSheet()->getStyle('A3')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

        // Save the file
        $fileName = tempNam(Kohana::config('yosemin.temp_dir'), 'yoseminXlsx');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save($fileName);

        download::force($fileName, NULL, 'export.xlsx');
    }

    public function allByAjax() {
        $resultList = $this->allData();
        $response->page = 1; // current page
        $response->total = 4; // total pages
        $response->records = count($resultList); // total records
        $response->rows = array();
        foreach ($resultList as $i => $row) {
            $rowData = $row;
            $rowData[2] = format::date($rowData['birth_date']);
            $response->rows[$i]['id'] = $row['id']; //id
            $response->rows[$i]['cell'] = $rowData;
        }
        die(json_encode($response));
    }

    public function display() {
        $id = $this->input->get('id');

        if (! valid::isId($id)) {
            $this->redirectToList('error.invalidId');
        }

        $person = $this->dao->fetchPersonById($id);
        if (! $person) {
            $this->redirectToList('error.recordNotFound');
        }

        $person['person_tag_ids'] = $this->dao->queryFirstValues('SELECT person_tag_id FROM person_tag_asoc WHERE person_id = :id', array('id' => $id));
        $this->viewData['personTagOptions'] = $this->dao->queryOptions(
                'SELECT id, name FROM person_tag ORDER BY name ASC', NULL, '');

        $this->loadFormData();

        $this->viewData['person'] = $person;
        $this->renderMySubview('personDisplay');
    }

    public function edit() {
        $id = $this->input->get('id');
        $person = array();

        if ($id) {
            $person = $this->dao->fetchPersonById($id);
            if (! $person) {
                $this->redirectToList('error.recordNotFound');
            }
            $person['person_tag_ids'] = $this->dao->queryFirstValues('SELECT person_tag_id FROM person_tag_asoc WHERE person_id = :id', array('id' => $id));
        }
        else {
            $person['is_admin'] = sql::$FALSE;
            // Set default insert values here
        }

        $this->loadFormData();

        $this->viewData['person'] = $person;
        $this->renderMySubview('personEdit');
    }

    protected function loadFormData() {
        $this->viewData['countryOptions'] = $this->dao->optionsCountry();

        $this->viewData['personTagOptions'] = $this->dao->queryOptions(
                'SELECT id, name FROM person_tag ORDER BY name ASC', NULL, '');
        
        $this->viewData['idcardTypeOptions'] = form::idcardOptions();
        
        $this->viewData['organizationOptions'] = $this->dao->optionsOrganizationsByCountry();
    }

    public function save() {
        $person = $this->postToDbValues();
        $id = $person['id'];

        $this->viewData['person'] = $person;

        // Basic validation
        $validator = new EntityValidator($person, 'person', $this->msg);
        $validator->required('first_name');
        $validator->required('last_name');
        $validator->required('gender');
        // $validator->date('birth_date');
        $validator->required('country_code');
        $validator->idOrNull('organization_id');
        $validator->applicationLanguage('preferred_language');
        // $validator->required('email');

        if ($this->msg->hasErrors()) {
            $this->loadFormData();
            $this->renderMySubview('personEdit');
            return;
        }

        // Database validation
        if ($person['organization_id']) {
        	$organization = $this->dao->getById('organization', $person['organization_id']);
        	if (! $organization) {
        		$this->msg->error('error-recordNotFound');
        		$this->loadFormData();
        		$this->renderMySubview('personEdit');
        		return;
        	}
        }
        
        $uniqueFields = array ('first_name', 'last_name', 'birth_date');
        if ($this->dao->alreadyExists('person', $uniqueFields, $person)) {
            $this->loadFormData();
            $this->msg->error('person.error-alreadyExists-names');
            $this->renderMySubview('personEdit');
            return;
        }

        // Insert or update the person
        if ($id != null) {
            $this->dao->update('person', $person);
            logger::securityInfo('Updated person #' . $person['id'] . ' ' . format::personFullName($person) . '.');
            $this->msg->info('person.editSuccess', format::personFullName($person));
        }
        else {
            $id = $this->dao->insert('person', $person);
            logger::securityInfo('Created person #' . $id . ' ' . format::personFullName($person) . '.');
            $this->msg->info('person.createSuccess', format::personFullName($person));
        }

        $this->dao->executeQuery('DELETE FROM person_tag_asoc WHERE person_id = :id', array('id' => $id));
        if ($person['person_tag_ids']) {
            foreach ($person['person_tag_ids'] as $tagId) {
                $this->dao->insert('person_tag_asoc', array('person_id' => $id, 'person_tag_id' => $tagId));
            }
        }

        $this->redirect('/admin/person/display?id=' . $id);
    }

    public function delete() {
        $id = $this->input->get('id');

        // Basic validation
        if (! valid::isId($id)) {
            $this->redirectToList('error.invalidId');
        }

        $person = $this->dao->fetchPersonById($id);
        if (! $person) {
            $this->redirectToList('error.recordNotFound');
        }

        try {
            $this->dao->getDb()->beginTransaction();

            // Delete registrations
            $this->dao->executeQuery('DELETE FROM registration WHERE person_id = :person_id', array('person_id' => $id));

            // Delete weekend registrations
            $this->dao->executeQuery('DELETE FROM vi_registration WHERE person_id = :person_id', array('person_id' => $id));

            // Delete person's account
            $this->dao->executeQuery('DELETE FROM account WHERE person_id = :person_id', array('person_id' => $id));

            // Delete the person
            $this->dao->delete('person', $id);

            logger::securityInfo('Deleted person #' . $id . ' ' . format::personFullName($person) . '.');
            
            $this->dao->getDb()->commit();
        }
        catch (Exception $e) {
            // If something raised an exception in our transaction block of statements,
            // roll back any work performed in the transaction
            $this->dao->getDb()->rollBack();
            $this->msg->error('error.dbExceptionWithMessage', $e->getMessage());
            Kohana::log('error', 'Error deleting a person #' . $id . ': ' . $e->getCode() . ', ' . $e->getMessage());
            $this->redirectBack();
            return;
        }

        $this->msg->info('person.deleteSuccess', format::personFullName($person));
        $this->redirectToList();
    }

    public function registrationList() {
        $id = $this->input->get('id');
        if (! valid::isId($id)) {
            $this->redirectToList('error.invalidId');
        }

        $person = $this->dao->fetchPersonById($id);
        if (! $person) {
            $this->redirectToList('error.recordNotFound');
        }
        $this->viewData['person'] = $person;

        $this->viewData['viRegistrationList'] = $this->dao->findViRegistrationsByPerson($id);
        $this->viewData['registrationList'] = $this->dao->findRegistrationsByPerson(
                $id, $this->getL10nLanguageCode());

        foreach ($this->viewData['viRegistrationList'] as &$viRegistration) {
            $viRegistration['paymentList'] = $this->dao->getByField('vi_registration_payment', 'vi_registration_id', $viRegistration['id'], 'payment_date ASC, payment_time ASC');
        }

        $this->renderMySubview('personRegistrationList');
    }

    public function accountForm() {
        $id = $this->input->get('id');
        if (! valid::isId($id)) {
            $this->redirectToList('error.invalidId');
        }

        $person = $this->dao->fetchPersonById($id);
        if (! $person) {
            $this->redirectToList('error.recordNotFound');
        }
        $this->viewData['person'] = $person;

        $account = $this->dao->getAccount($id);
        if ($account) {
            $formData = $account;
        }
        else {
            $formData = array(
                'person_id'  => $id,
                'is_admin'   => sql::$FALSE,
                'is_blocked' => sql::$FALSE,
                'login'      => auth::suggestLogin($person, $this->dao)
            );
        }
        $this->viewData['formData'] = $formData;

        $this->renderMySubview('personAccountEdit');
    }

    public function accountSave() {
        $formData = $this->postToDbValues();
        $this->viewData['formData'] = $formData;
        $personId = $formData['person_id'];

        if (! valid::isId($personId)) {
            $this->redirectToList('error.invalidId');
        }

        $person = $this->dao->fetchPersonById($personId);
        if (! $person) {
            $this->redirectToList('error.recordNotFound');
        }
        $this->viewData['person'] = $person;

        // Basic validation
        $validator = new EntityValidator($formData, 'person', $this->msg);
        $validator->required('login');
        if (! sql::isPersistent($formData)) {
            $validator->minLength('password', 5);
        }
        $validator->boolean('is_admin');
        $validator->boolean('is_blocked');

        if ($this->msg->hasErrors()) {
            $this->loadFormData();
            $this->publishErrors('/admin/person/personAccountEdit');
            return;
        }

        // Database validation
        if ($this->dao->alreadyExists('account', 'login', $formData, 'person_id')) {
            $this->loadFormData();
            $this->msg->error('person.error-alreadyExists-login');
            $this->publishErrors('/admin/person/personAccountEdit');
            return;
        }

        // Insert or update the person
        if ($formData[sql::$ENTITY_VERSION_KEY] != null) {
            $this->dao->update('account', $formData, 'person_id');
            logger::securityInfo('Updated account of person #' . $personId . ' ' . format::personFullName($person));
            $this->msg->info('person.accountEditSuccess', format::personFullName($person));
        }
        else {
            $this->dao->insert('account', $formData);
            $this->msg->info('person.accountCreateSuccess', format::personFullName($person));
        }

        $this->redirect('/admin/person/display?id=' . $personId);
    }

    public function changePasswordForm() {
        $id = $this->input->get('id');
        if (! valid::isId($id)) {
            $this->redirectToList('error.invalidId');
        }

        $person = $this->dao->fetchPersonById($id);
        $account = $this->dao->getAccount($id);
        if (! $person || ! $account) {
            $this->redirectToList('error.recordNotFound');
        }
        $this->viewData['person'] = $person;

        $this->viewData['formData'] = $account;
        $this->renderMySubview('personChangePassword');
    }

    public function changePassword() {
        $formData = $this->postToDbValues();
        $this->viewData['formData'] = $formData;
        $personId = $formData['person_id'];

        if (! valid::isId($personId)) {
            $this->redirectToList('error.invalidId');
        }

        $person = $this->dao->fetchPersonById($personId);
        $account = $this->dao->getAccount($personId);
        if (! $person || ! $account) {
            $this->redirectToList('error.recordNotFound');
        }
        $this->viewData['person'] = $person;

        // Basic validation
        $validator = new EntityValidator($formData, 'person', $this->msg);
        $validator->minLength('new_password', 5);

        if ($this->msg->hasErrors()) {
            $this->loadFormData();
            $this->publishErrors('/admin/person/personChangePassword');
            return;
        }

        $account[sql::$ENTITY_VERSION_KEY] = $formData[sql::$ENTITY_VERSION_KEY];
        $account['password'] = auth::passwordHash($account['login'], $formData['new_password']);
        $this->dao->update('account', $account, 'person_id');

        $this->msg->info('person.changePasswordSuccess', format::personFullName($person));
        $this->redirect('/admin/person/display?id=' . $personId);
    }

    public function mergeDuplicatesForm() {
        $id = $this->input->get('id');
        if (! valid::isId($id)) {
            $this->redirectToList('error.invalidId');
        }

        $person = $this->dao->fetchPersonById($id);
        if (! $person) {
            $this->redirectToList('error.recordNotFound');
        }
        $this->viewData['person'] = $person;
        
        $this->viewData['formData'] = array(
            'person_id' => $id,
            'removePerson_id' => $id
        );

        $this->mergeDuplicatesData();

        $this->renderMySubview('mergeDuplicates');
    }

    private function mergeDuplicatesData() {
        $options = $this->dao->queryOptions('SELECT id, last_name, first_name, birth_date ' .
                'FROM person ORDER BY last_name, first_name, birth_date', NULL, array(' ', '()'));

        $this->viewData['personToKeepOptions'] = $options;
        $this->viewData['personToRemoveOptions'] = $options;
    }

    public function mergeDuplicates() {
        $formData = $this->postToDbValues();
        $this->viewData['formData'] = $formData;
        $keepPersonId = $formData['keepPerson_id'];
        $removePersonId = $formData['removePerson_id'];

        if (! valid::isId($keepPersonId) || ! valid::isId($removePersonId)) {
            $this->mergeDuplicatesData();
            $this->redirectToList('error.invalidId');
        }

        $personToKeep = $this->dao->fetchPersonById($keepPersonId);
        $personToRemove = $this->dao->fetchPersonById($removePersonId);
        if (! $personToKeep || ! $personToRemove) {
            $this->redirectToList('error.recordNotFound');
        }
        $this->viewData['person'] = $person;

        $ev = new EntityValidator($formData, 'person', $this->msg);
        $ev->id('keepPerson_id');
        $ev->id('removePerson_id');
        if ($keepPersonId && $keepPersonId == $removePersonId) {
            $this->msg->error('person.cannotMergeToItself');
        }
        if ($this->msg->hasErrors()) {
            $this->mergeDuplicatesData();
            $this->publishErrors('/admin/person/mergeDuplicates');
            return;
        }

        try {
            $this->dao->getDb()->beginTransaction();

            $queryOptions = array('personToRemoveId' => $removePersonId, 'personToKeepId' => $keepPersonId);

            // If the person to keep does not have an account and the person to delete has one, move it.
            if (! $personToKeep['login'] && $personToRemove['login']) {
                $this->dao->executeQuery('UPDATE account SET person_id = :personToKeepId WHERE person_id = :personToRemoveId',
                        $queryOptions);
                $this->msg->info('person.mergeDuplicates-accoutMoved', format::personFullName($personToKeep));
            }

            // Move all registrations on the person to keep.
            $registrationCount = $this->dao->queryCount('SELECT count(*) FROM registration WHERE person_id = :personToRemoveId', array('personToRemoveId' => $removePersonId));
            if ($registrationCount > 0) {
                $this->dao->executeQuery('UPDATE registration SET person_id = :personToKeepId WHERE person_id = :personToRemoveId', $queryOptions);
                $this->msg->info('person.mergeDuplicates-registrationsMoved', $registrationCount, format::personFullName($personToKeep));
            }
            $viRegistrationCount = $this->dao->queryCount('SELECT count(*) FROM vi_registration WHERE person_id = :personToRemoveId', array('personToRemoveId' => $removePersonId));
            if ($viRegistrationCount > 0) {
                $this->dao->executeQuery('UPDATE vi_registration SET person_id = :personToKeepId WHERE person_id = :personToRemoveId', $queryOptions);
                $this->msg->info('person.mergeDuplicates-viRegistrationsMoved', $viRegistrationCount, format::personFullName($personToKeep));
            }
            
            // Remove the unwanted person records
            if ($personToRemove['login']) {
                $this->dao->delete('account', array('person_id' => $removePersonId));
            }
            $this->dao->deleteById('person', $removePersonId);
            
            $this->dao->getDb()->commit();
            $this->msg->info('person.mergeDuplicates-success', format::personFullName($personToRemove));
        }
        catch (Exception $e) {
            $this->dao->getDb()->rollBack();
            $this->msg->error('error.dbException', $e->getCode());
            Kohana::log('error', $e);
        }

        $this->redirect('/admin/person/display?id=' . $keepPersonId);
    }

    protected function searchData() {
        $this->loadFormData();
        $this->viewData['genderOptions'] = array(
            'M' => text::get('person.gender-M'),
            'F' => text::get('person.gender-F')
        );

        $this->viewData['personTagOptions'] = $this->dao->queryOptions('SELECT id, name FROM person_tag ORDER BY name');
    }

    public function personDuplicates() {
        $this->viewData['personList'] = $this->dao->findPersonDuplicates();
        $this->renderMySubview('personDuplicates');
    }
}
